package common.project.epic.db;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class EpicDBRTAdapter {
	private static final String DATABASE_NAME = "epicDatabaseRuntime.db";
	private static final int DATABASE_VERSION = 1;
	
	// database content table
	private static final String BLACKLIST_TABLE = "blacklist";
	private static final String HISTORY_TABLE = "history";
	private static final String DISCOVERED_TABLE = "discovered";
	private Cursor cursor;
	
	// unique incremental id
	public static final String KEY_ID = "_id";
	public static final int IND_ID = 0;
	
    // variable to hold the database instance
    private static SQLiteDatabase db;
    // context of the application using the database
    private final Context context;
    // database open/update helper
    private EpicDbHelper dbHelper;
    private static final String devicesDiscovered = "create table discovered ( mac TEXT PRIMARY KEY NOT NULL UNIQUE, name TEXT NOT NULL, rssi TEXT NOT NULL , discoveredat NUMERIC NOT NULL);";
    private static final String devicesBlackList = "create table blacklist (mac TEXT PRIMARY KEY NOT NULL  UNIQUE , expiry NUMERIC NOT NULL);";
    private static final String devicesHistory = "create table history (mac TEXT PRIMARY KEY NOT NULL  UNIQUE , contact_at NUMERIC NOT NULL);";
	
    private static final String MAC = "mac";
    private static final String NAME = "name";
    private static final String RSSI = "rssi";
    private static final String DISC = "discoveredat";
    //database query string
    private static final String whereClause = "1";
    
    public EpicDBRTAdapter(Context _context) {
    	context = _context;
    	dbHelper = new EpicDbHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    
    public EpicDBRTAdapter open() throws SQLException {
    	try {
    		db = dbHelper.getWritableDatabase();
    	}
    	catch(SQLiteException ex) {
    		db = dbHelper.getReadableDatabase();
    	}
    	return this;
    }
    
    public void close() {
    	
    	db.close();
  
    }
    //Add Methods
    public long addDeviceHistory(EpicDevice device) {
    	
    	ContentValues newContent = new ContentValues();
    	newContent.put("mac", device.getMac());
    	newContent.put("name", device.getName());
    	newContent.put("contact_at", device.getContact_at());
    	
    	return db.insert(HISTORY_TABLE, null, newContent);
    }
    public long addDeviceBlackList(EpicDevice device) {
    	ContentValues newContent = new ContentValues();
    	newContent.put("mac", device.getMac());
    	newContent.put("name", device.getName());
    	if(device.getExpiry()==0){newContent.put("expiry",device.getExpiry());}
    	return db.insert(BLACKLIST_TABLE, null, newContent);
    }

    public long addDeviceDiscovered(EpicDevice device) {
    	
    	ContentValues newContent = new ContentValues();
    	newContent.put(MAC, device.getMac());
    	newContent.put(NAME, device.getName());
    	newContent.put(DISC,device.getDiscovered_at());
    	Short rssi= new Short(device.getRssi());
    	newContent.put(RSSI, rssi.toString());
    	return db.insert(DISCOVERED_TABLE, null, newContent);
    	
    } 
    
    // Remove methods
    public boolean removeDiscoveredDevice(EpicDevice device) {
    	String mac = device.getMac();
    	String whereClause = "mac = ?";
		String[] whereArgs = {mac};
    	return db.delete(DISCOVERED_TABLE,whereClause,whereArgs)>0;
    }
    public boolean removeHistoryDevice(EpicDevice device) {
    	String mac = device.getMac();
    	String whereClause = "mac = ?";
		String[] whereArgs = {mac};
    	return db.delete(HISTORY_TABLE,whereClause,whereArgs)>0;
    }
    public boolean removeBlackListDevice(EpicDevice device) {
    	String mac = device.getMac();
    	String whereClause = "mac = ?";
		String[] whereArgs = {mac};
    	return db.delete(BLACKLIST_TABLE,whereClause,whereArgs)>0;
    }
	public int RemoveAllDiscovered(){		
		return db.delete(DISCOVERED_TABLE, null, null);
		//return db.delete(DISCOVERED_TABLE, whereClause, null);
	}
	public int RemoveAllHistory(){		
		String whereClause = "1";
		return db.delete(HISTORY_TABLE, whereClause, null);//restituisce il numero di occorrenze eliminate
	}
	public int RemoveAllBlackList(){		
		String whereClause = "1";
		return db.delete(BLACKLIST_TABLE, whereClause, null);//restituisce il numero di occorrenze eliminate
	}
    
    //get methods
    public Cursor getDiscoveredDevices() {
    	String selection = null;   
    	String[] columns = null;
    	String groupBy = null;
    	String having = null;
    	String[] selectionArgs = null;
    	String orderBy = null; 
    	String limit = null;
    	cursor = db.query(DISCOVERED_TABLE, new String[] { MAC, NAME, RSSI, DISC}, null, null, null, null, null);
    	return cursor;
    }
    
    
    public Cursor getHistoryDevices() {
    	String select = null;
    	String[] args = null;
    	String groupBy = null;
    	String having = null;
    	String orderBy = "DESC contact_at";
    	String limit = null;
    	Cursor cursor = db.query(HISTORY_TABLE,null,select,args,groupBy, orderBy, limit);
    	return cursor;
    }
    
    public Cursor getBlackListDevices() {
    	String select = null;
    	String[] args = null;
    	String groupBy = null;
    	String having = null;
    	String orderBy = "DESC expiry";
    	String limit = null;
    	Cursor cursor = db.query(BLACKLIST_TABLE,null,select,args,groupBy, orderBy, limit);
    	return cursor;
    }

    private static class EpicDbHelper extends SQLiteOpenHelper {
    	
    	public EpicDbHelper(Context context, String name, CursorFactory factory, int version) {
    		super(context, name, factory, version);
    	}
    	
    	@Override
    	public void onCreate(SQLiteDatabase _db) {
    		
    		//tabella in cui inserisco i dispositivi trovati durante la fase di inquiry
    		
    		_db.execSQL(devicesDiscovered);
    		//tabella dei MAC address in blacklist 
    		
    		_db.execSQL(devicesBlackList);
    		
    		//tabella dei MAC address già contattati nell'ultimo periodo di durata N 
    		_db.execSQL(devicesHistory);
    		
    	}
    	
    	@Override
    	public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) {
    		_db.execSQL("DROP TABLE (IF ALREADY EXIST) " + DISCOVERED_TABLE);
    		onCreate(_db);
    	}
    }
    
}
